# -*- coding:utf-8 -*-
# @FileName :test2.py
# @user chunxiangjie
# @Time :2022/8/23 13:30
# -*- coding:utf-8 -*-
import tkinter as tk  # 使用tkinter前需要先导入
from tkinter import filedialog, messagebox, ttk
from openpyxl import load_workbook
from openpyxl.styles import alignment
import os
import pandas as pd

# 建立窗口window
window = tk.Tk()
window.title('excel合并单元格工具')
w_width = 630
w_height = 600
scn_width = window.maxsize()[0]
x_point = (scn_width - w_width) // 2
window.geometry('%dx%d+%d+%d' % (w_width, w_height, x_point, 100))
window.wm_attributes('-topmost', True)
window.tk_focusFollowsMouse()
# window.bind("<escape>", lambda event: window.iconify())
path_tar = tk.StringVar()
sheetvar = tk.StringVar()  # 目标工作表
# 打开目标文件
def getmergefile():
    file_path = filedialog.askopenfilename(title=u'选择文件', filetypes=[('excel', '.xlsx')])
    path_tar.set(file_path)
    alldata = pd.read_excel(file_path, None)
    ttk.Label(frame1, text="请选择目标工作表:").grid(row=1, column=0, sticky='w')
    global sheetvar
    chosen_sheet = ttk.Combobox(frame1, width=16, textvariable=sheetvar)
    chosen_sheet['values'] = list(alldata)
    chosen_sheet.grid(row=1, column=1, sticky='w')
    chosen_sheet.bind("<<comboboxselected>>", lambda event: getmergeseg(event, alldata, sheetvar.get()))
# 勾选目标字段
def getmergeseg(event, alldata, sheet):
    global frame2,segvar
    segvar = []
    try:
        frame2.destroy()
    except:
        pass
    frame2 = tk.Frame(window, padx=15, pady=6)
    frame2.grid(row=1, column=0, sticky='w')
    ttk.Label(frame2, text="请勾选分组合并的目标字段(第一个勾选框为分组合并依据，必须事先进行排序:").grid(row=0, column=0, columnspan=4, sticky='w')
    data = alldata[sheet]
    for index, item in enumerate(data.columns):
        segvar.append(tk.StringVar())
        ttk.Checkbutton(frame2, text=item, variable=segvar[-1], onvalue=item, offvalue='').grid(row=(index // 4 + 1),column=index % 4,sticky='w')
# 合并字段单元格
def merging(file, sheet,segvar):
    selected = [i.get() for i in segvar if i.get()]
    df = pd.read_excel(file, sheet)
    wb = load_workbook(file)
    ws = wb[sheet]
    mergecells(ws, df, selected)
    try:
        wb.save(file)
        messagebox.showinfo('提示', file + '-' + sheet + '指定单元格合并完成')
        os.system('start ' + os.path.dirname(file))
    except Exception as e:
        messagebox.showerror('警告', str(e))
# 合并单元格函数
def mergecells(ws,df,cols):
    col = cols[0]
    gdic = df.groupby(col).groups
    aligncenter = alignment(horizontal='center',vertical='center')
    for gname in gdic:
        indexs = gdic[gname] + 2
        indexs = indexs.sort_values()
        for col in cols:  # 每一个要合并的字段
            colindex = df.columns.tolist().index(col) + 1
            ws.merge_cells(start_row=indexs[0], end_row=indexs[-1], start_column=colindex, end_column=colindex)  # 合并
            for i in range(1, ws.max_row + 1):  # 实现居中
                ws.cell(row=i, column=colindex).alignment = aligncenter
def manual():  # 使用说明
    info = """作用是合并单元格，把同样内容的单元格合并到一起，所以必须实现对目标字段进行排序，否则无法实现合并"""
    messagebox.showinfo('提示', info)


frame1 = tk.Frame(window, pady=6, padx=15)
frame1.grid(row=0, column=0, sticky='w')
ttk.Button(frame1, text="打开目标文件", command=getmergefile).grid(row=0, column=0, sticky='w')
ttk.Entry(frame1, textvariable=path_tar, width=40).grid(row=0, column=1)
frame3 = tk.Frame(window, pady=10, padx=15)
frame3.grid(row=2, column=0, sticky='w')
ttk.Button(frame3,text="点击合并单元格",command=lambda:merging(path_tar.get(),sheetvar.get(),segvars)).grid(row=0,column=0,sticky='w')
ttk.Button(frame3, text="使用说明", command=manual).grid(row=0, column=1)

window.mainloop()